﻿Imports System.IO
Imports System.Data.SqlClient

Public Module modQueueReporting

    Public Sub UpdateBatchEvents _
    (ByVal oWorkflowData As Kofax.ACWFLib.ACWorkflowData, _
     ByVal oBatchElement As ACDataElement, _
     ByVal oKofaxBatch As KofaxXmlSchema.ImportSession.Batch, _
     ByVal SqlConnStr As String, _
     ByVal BatchFields() As String, _
     ByVal FolderFields() As String, _
     ByVal DocumentFields() As String)

        Dim oSqlConnection As New SqlConnection(SqlConnStr)
        Dim BatchGuid As String = oKofaxBatch.Name
        Dim oHistory As KofaxXmlSchema.ImportSession.Batch.BatchHistoryEntry
        Dim iPageCount As Integer = 0
        Dim iBatchdbID As Integer

        oHistory = oKofaxBatch.BatchHistoryEntries(oKofaxBatch.BatchHistoryEntries.Length - 1)

        For Each oField As KofaxXmlSchema.ImportSession.Batch.BatchField In oKofaxBatch.BatchFields
            If UCase(oField.Name) = ("GUID") Then
                If Len(oField.Value) > 0 Then
                    BatchGuid = oField.Value
                    Exit For
                End If
            End If
        Next

        'Get the pagecount for the batch.
        iPageCount = GetBatchPageCount(oKofaxBatch)

        Dim oCmd As New SqlCommand("usp_SetBatchEvent", oSqlConnection)
        oCmd.CommandType = CommandType.StoredProcedure
        oCmd.Parameters.Add(New SqlParameter("@BATCHGUID", BatchGuid))
        oCmd.Parameters.Add(New SqlParameter("@BatchClassName", oBatchElement.AttributeValue("BatchClassName")))
        oCmd.Parameters.Add(New SqlParameter("@StartTime", oHistory.StartDateTime))
        oCmd.Parameters.Add(New SqlParameter("@EndTime", oHistory.EndDateTime))
        oCmd.Parameters.Add(New SqlParameter("@UserName", Environment.UserName))
        oCmd.Parameters.Add(New SqlParameter("@ModuleName", oWorkflowData.CurrentModule.Name.ToString))
        oCmd.Parameters.Add(New SqlParameter("@NextModuleName", oWorkflowData.NextModule.Name.ToString))
        oCmd.Parameters.Add(New SqlParameter("@BatchStateName", oWorkflowData.NextState.Name.ToString))
        oCmd.Parameters.Add(New SqlParameter("@BatchPriority", oBatchElement.AttributeValue("Priority")))
        oCmd.Parameters.Add(New SqlParameter("@PageCount", iPageCount))

        Try
            If oSqlConnection.State = ConnectionState.Closed Then
                oSqlConnection.Open()
            End If

            iBatchdbID = oCmd.ExecuteScalar()

            'Immediately close the connection.
            oSqlConnection.Close()
        Finally
            If oSqlConnection.State = ConnectionState.Open Then
                oSqlConnection.Close()
            End If
        End Try

        'Insert the batch level tracking fields into the reporting db.
        For Each sField As String In BatchFields
            If sField.Length > 0 Then
                For Each oField As KofaxXmlSchema.ImportSession.Batch.BatchField In oKofaxBatch.BatchFields
                    If UCase(oField.Name) = sField Then
                        InsertEventField("Batch", iBatchdbID, oField.Name, oField.Value, SqlConnStr)
                    End If
                Next
            End If
        Next

        Dim iFolderID As Integer = 1
        'If folders exist insert db field data.
        For Each oFolder As KofaxXmlSchema.Folder In oKofaxBatch.Folders
            For Each oDoc As KofaxXmlSchema.Document In oFolder.Documents

                'Insert the document details.
                UpdateDocDetails(iBatchdbID, oDoc, SqlConnStr, DocumentFields)

                'Insert the folder fields
                For Each sField As String In FolderFields
                    For Each oField As KofaxXmlSchema.IndexField In oFolder.IndexFields
                        If UCase(oField.Name) = UCase(sField) Then
                            InsertEventField("Folder", iBatchdbID, iFolderID & "_" & oField.Name, oField.Value, SqlConnStr)
                        End If
                    Next
                Next
            Next
            iFolderID += 1
        Next

        'Insert the document level data.
        For Each oDoc As KofaxXmlSchema.Document In oKofaxBatch.Documents
            UpdateDocDetails(iBatchdbID, oDoc, SqlConnStr, DocumentFields)
        Next

    End Sub

    'Document db insert logic.
    Public Sub UpdateDocDetails(ByVal BatchEventID As Integer, ByVal Document As KofaxXmlSchema.Document, ByVal SqlConnStr As String, ByVal DocumentFields() As String)
        Dim iDocID As Integer
        Dim Classification_Result As String = ""
        Dim oSqlConnection As New SqlConnection(SqlConnStr)
        Dim oCmd As New SqlCommand("usp_SetDocEvent", oSqlConnection)

        For Each oField As KofaxXmlSchema.IndexField In Document.IndexFields
            If oField.Name = "Classification Result" Then
                Classification_Result = oField.Value
                Exit For
            End If
        Next

        If Classification_Result.Length = 0 Then : Classification_Result = Document.FormTypeName : End If

        oCmd.CommandType = CommandType.StoredProcedure
        oCmd.Parameters.Add(New SqlParameter("@BatchEventID", BatchEventID))
        oCmd.Parameters.Add(New SqlParameter("@BatchDocGUID", Document.BatchDocGUID))
        oCmd.Parameters.Add(New SqlParameter("@Classification_Result", Classification_Result))
        oCmd.Parameters.Add(New SqlParameter("@PageCount", Document.Pages.Length))

        Try
            If oSqlConnection.State = ConnectionState.Closed Then
                oSqlConnection.Open()
            End If

            iDocID = oCmd.ExecuteScalar()

            'Immediately close the connection.
            oSqlConnection.Close()
        Finally
            If oSqlConnection.State = ConnectionState.Open Then
                oSqlConnection.Close()
            End If
        End Try

        'Insert the document fields for traking into the db.
        For Each sField As String In DocumentFields
            If sField.Length > 0 Then
                For Each oField As KofaxXmlSchema.IndexField In Document.IndexFields
                    If UCase(oField.Name) = UCase(sField) Then
                        InsertEventField("Document", iDocID, oField.Name, oField.Value, SqlConnStr)
                    End If
                Next
            End If
        Next

    End Sub

    'Field db insert logic.
    Public Sub InsertEventField(ByVal ObjectLevelName As String, ByVal ParentID As Integer, ByVal FieldName As String, ByVal FieldValue As String, ByVal SqlConnStr As String)
        Dim oSqlConnection As New SqlConnection(SqlConnStr)
        Dim oCmd As New SqlCommand("usp_SetEventField", oSqlConnection)

        oCmd.CommandType = CommandType.StoredProcedure
        oCmd.Parameters.Add(New SqlParameter("@ObjectName", ObjectLevelName))
        oCmd.Parameters.Add(New SqlParameter("@ParentID", ParentID))
        oCmd.Parameters.Add(New SqlParameter("@FieldName", FieldName))
        oCmd.Parameters.Add(New SqlParameter("@FieldValue", FieldValue))

        Try
            If oSqlConnection.State = ConnectionState.Closed Then
                oSqlConnection.Open()
            End If

            oCmd.ExecuteScalar()

            'Immediately close the connection.
            oSqlConnection.Close()
        Finally
            If oSqlConnection.State = ConnectionState.Open Then
                oSqlConnection.Close()
            End If
        End Try

    End Sub

    Public Function GetBatchPageCount(ByVal oBatch As KofaxXmlSchema.ImportSession.Batch) As Integer
        Dim iPageCount As Integer = 0

        If Not (oBatch.Pages) Is Nothing Then
            iPageCount += oBatch.Pages.Length
        End If

        If Not (oBatch.Folders) Is Nothing Then
            For Each oFolder As KofaxXmlSchema.Folder In oBatch.Folders
                If Not oFolder.Documents Is Nothing Then
                    For Each oDoc As KofaxXmlSchema.Document In oFolder.Documents
                        iPageCount += oDoc.Pages.Length
                    Next
                End If
            Next
        End If

        If Not oBatch.Documents Is Nothing Then
            For Each oDoc As KofaxXmlSchema.Document In oBatch.Documents
                iPageCount += oDoc.Pages.Length
            Next
        End If

        Return iPageCount
    End Function

End Module
